﻿using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.Filters;
using Microsoft.AspNetCore.Mvc.Formatters;
using Microsoft.AspNetCore.WebUtilities;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace WebLog.Db
{
    /// <summary>
    /// SQLite 助手
    /// </summary>
    public sealed class SQLiteHelper
    {
        public const string dbName = "weblog.db";
        public string dbPath = "";//手工指定db的物理路径，如果为空，则会取当前运行目录
        private string _ConnectionStr;
        private string ConnectionStr
        {
            get
            {
                if (_ConnectionStr == null)
                {
                    var testSrc = Directory.GetCurrentDirectory();
                    if (string.IsNullOrEmpty(dbPath))
                    {
                        if (testSrc.LastIndexOf("\\bin") != -1)
                            testSrc = testSrc.Substring(0, testSrc.LastIndexOf("\\bin"));
                        if (testSrc.Contains("\\HCD.Web"))//如果是web主项目 就判断weblog有没有
                            testSrc = testSrc.Substring(0, testSrc.LastIndexOf("\\HCD.Web") + 8) + "\\weblog\\" + SQLiteHelper.dbName;
                        else//如果不是web主项目 使用 web项目的db文件
                            testSrc = testSrc.Substring(0, testSrc.LastIndexOf("\\")) + "\\HCD.Web\\" + SQLiteHelper.dbName;
                        var connStr = File.Exists(testSrc) ? testSrc : null;
                        _ConnectionStr = "Data Source=" + (connStr ?? dbName);
                    }
                    else
                        _ConnectionStr = "Data Source=" + (dbPath + SQLiteHelper.dbName);
                    return _ConnectionStr;
                }
                else
                    return _ConnectionStr;
            }
        }
        private static SQLiteHelper _instance;
        Semaphore semaphoreTran = new Semaphore(initialCount: 1, maximumCount: 1);
        /// <summary>
        /// 获取实例
        /// </summary>
        /// <returns></returns>
        public static SQLiteHelper GetInstance()
        {
            if (_instance == null)
                lock (typeof(SQLiteHelper))
                    if (_instance == null)
                        _instance = new SQLiteHelper();
            return _instance;
        }

        private SQLiteHelper()
        {
        }

        SQLiteCommand HandCMDPms(SQLiteCommand cmd, params SQLiteParameter[] sQLiteParameters)
        {
            if (sQLiteParameters != null &&
                (sQLiteParameters = sQLiteParameters.Where(s => s != null).ToArray()).Length > 0)
                cmd.Parameters.AddRange(sQLiteParameters);
            return cmd;
        }
        /// <summary>
        /// 获取数据表
        /// </summary>
        /// <param name="cmdText"> 需要执行的命令文本 </param>
        /// <returns> 一个数据表集合 </returns>
        public DataTable GetDataTable(string cmdText, string _ConnectionStr = null, params SQLiteParameter[] sQLiteParameters)
        {
            var dt = new DataTable();

            try
            {
                using (var conn = new SQLiteConnection(_ConnectionStr ?? ConnectionStr))
                {
                    conn.Open();
                    var cmd = new SQLiteCommand(conn) { CommandText = cmdText };
                    HandCMDPms(cmd, sQLiteParameters);
                    using (var reader = cmd.ExecuteReader())
                    {
                        dt.Load(reader);
                    }
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }

            return dt;
        }
        /// <summary>
        /// 执行非查询命令
        /// </summary>
        /// <param name="cmdText"> 需要执行的命令文本 </param>
        /// <returns> 返回更新的行数 </returns>
        public async Task<int> ExecuteNonQueryAsync(string cmdText, bool isOpenTran = true, params SQLiteParameter[] sQLiteParameters)
        {
            if (cmdText == null || cmdText.Trim() == "") return -1;
            using (var conn = new SQLiteConnection(ConnectionStr))
            {
                conn.Open();
                var tran = isOpenTran ? conn.BeginTransaction() : null;
                var cmd = new SQLiteCommand(conn) { CommandText = cmdText };
                HandCMDPms(cmd, sQLiteParameters);
                var result = 0;
                try
                {
                    result = _Run(tran, cmd);
                }
                catch (Exception ex)
                {
                    //if (ex.Message.Contains("database is locked"))
                    //{
                    //    SysLogHelper.GetInstance().AppendSysLog("执行代码异常," + ex.Message);
                    //    Thread.Sleep(500);
                    //}
                    //else 
                    throw ex;
                }
                finally
                {
                    if (tran != null)
                        tran.Dispose();
                    conn.Dispose();
                }
                return result;
            }
        }


        /// <summary>
        /// 执行非查询命令
        /// </summary>
        /// <param name="cmdText"> 需要执行的命令文本 </param>
        /// <returns> 返回更新的行数 </returns>
        public int ExecuteNonQuery(string cmdText, bool isOpenTran = true, params SQLiteParameter[] sQLiteParameters)
        {
            if (cmdText == null || cmdText.Trim() == "") return -1;
            using (var conn = new SQLiteConnection(ConnectionStr))
            {
                conn.Open();
                var tran = isOpenTran ? conn.BeginTransaction() : null;
                var cmd = new SQLiteCommand(conn) { CommandText = cmdText };
                HandCMDPms(cmd, sQLiteParameters);
                return _Run(tran, cmd);
            }
        }
        /// <summary>
        /// 根据事务来限制执行 有事务未释放的时候必须等待前面的
        /// </summary>
        /// <param name="tran"></param>
        /// <param name="cmd"></param>
        /// <returns></returns>
        private int _Run(SQLiteTransaction tran, SQLiteCommand cmd)
        {
            if (tran != null) semaphoreTran.WaitOne();
            try
            {
                var rowsUpdated = cmd.ExecuteNonQuery();
                if (tran != null)
                {
                    tran.Commit();
                    tran.Dispose();
                }
                return rowsUpdated;
            }
            catch (Exception ex)
            {
                if (tran != null)
                {
                    tran.Rollback();
                    tran.Dispose();
                }
                throw ex;
                return -1;
            }
            finally
            {
                if (tran != null) semaphoreTran.Release();
            }
        }

        /// <summary>
        /// 执行检索单项命令
        /// </summary>
        /// <param name="cmdText"> 需要执行的命令文本 </param>
        /// <returns> 一个字符串 </returns>
        public string ExecuteScalar(string cmdText, string _ConnectionStr = null)
        {
            using (var conn = new SQLiteConnection(_ConnectionStr ?? ConnectionStr))
            {
                conn.Open();
                var cmd = new SQLiteCommand(conn) { CommandText = cmdText };
                var value = cmd.ExecuteScalar();

                if (value != null)
                {
                    return value.ToString();
                }
            }

            return "";
        }
    }
}